<html>
<head><title>CS143 Project 1B</title></head>
<body>
<h1>Simple SQL Query</h1>
<form action="query.php" method="GET">
<textarea name="query" cols="60" rows="8"><?php echo $_GET{"query"}; ?></textarea>
<input type="submit" value="Submit" />
</form>
</p>
<p><small>Note: tables and fields are case sensitive. Run "SHOW TABLES;" to see the list of
available tables.</small>
</p>

<?php

$query=$_GET["query"]; // Gets user input

// Check for valid query type
// Only SHOW and SELECT queries supported
/*
$query_lower = strtolower($query);
$query_lower_len = strlen($query_lower);
if ($query_lower_len < 4)
{
    print "Only SHOW and SELECT queries allowed.";
    exit(1);
}
else if (substr($query_lower, 0, 4) != "show")
{
    if ($query_lower_len < 6)
    {
        print "Only SHOW and SELECT queries allowed.";
        exit(1);
    }
    else if (substr($query_lower, 0, 6) != "select")
    {
        print "Only SHOW and SELECT queries allowed.";
        exit(1);       
    }
}
*/

if ($query != "")
{
    /*
    // Check if user query ends with ';'
    $query_len = strlen($query);
    if (substr($query, $query_len-1, 1) != ';')
        $query = $query . ';';
    */
    
    // Set up connection to the database
    $db_connection = mysql_connect("localhost", "cs143", "");
    if(!$db_connection) {
        $errmsg = mysql_error($db_connection);
        print "Connection failed: $errmsg <br />";
        exit(1);
    }
    mysql_select_db("CS143", $db_connection);
    
    $rs = mysql_query($query, $db_connection); // Issue query
    
    echo '<h3>Results from MySQL:</h3>';
    
    if ($rs == TRUE)
    {
        // Retrieve Results
        // Put the results into a table
        echo '<table border=1 cellspacing=1 cellpadding=2>';
        echo '<tr align=center>';
        $i_limit = mysql_num_fields($rs);
        for ($i=0; $i < $i_limit; $i++)
        {
            $col_name = mysql_fieldname($rs, $i);
            echo '<td><b>', $col_name, '</b></td>';
        }
        echo '</tr>';
        
        while($row = mysql_fetch_row($rs)) {
            echo '<tr>';
            foreach($row as $key=>$value)
            {
                if ($value != NULL)
                {
                    $output = $value;
                    if (substr($value, 0, 1) == '"')
                    {
                        $length = strlen($value);
                        $output = substr($value, 1, $length-2);
                    }
                    echo '<td>', $output, '</td>';
                }
                else
                    echo '<td>', 'N/A', '</td>';
            }
            echo '</tr>';
        }
        echo '</table>';
    }
    else
    {
        print "SQL syntax error.";
    }
    
    mysql_close($db_connection); // Close connection with database when finished
}
?>

</body>
</html>